# Order related functions
from datetime import datetime
from app.utils.database import get_db_cursor

def create_order(user_id, total_price):
    """Create order"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("""
            INSERT INTO orders (user_id, total_price, status, created_at)
            VALUES (%s, %s, %s, %s)
            RETURNING order_id
        """, (user_id, total_price, 'pending', datetime.now()))
        order_id = cursor.fetchone()['order_id']
        conn.commit()
        return order_id
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def add_order_item(order_id, category, product_id, quantity, price):
    """Add order item"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("""
            INSERT INTO order_items (order_id, category, product_id, quantity, price)
            VALUES (%s, %s, %s, %s, %s)
        """, (order_id, category, product_id, quantity, price))
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def create_order_with_items(user_id, total_price, order_items_data):
    """Create order with all order items (transaction handling)"""
    conn, cursor = get_db_cursor()
    try:
        # Start transaction
        cursor.execute("BEGIN")
        
        # Create order
        cursor.execute("""
            INSERT INTO orders (user_id, total_price, status, created_at)
            VALUES (%s, %s, %s, %s)
            RETURNING order_id
        """, (user_id, total_price, 'pending', datetime.now()))
        order_id = cursor.fetchone()['order_id']
        
        # Create order items
        for item_data in order_items_data:
            cursor.execute("""
                INSERT INTO order_items (order_id, category, product_id, quantity, price)
                VALUES (%s, %s, %s, %s, %s)
            """, (order_id, item_data['category'], item_data['product_id'], 
                  item_data['quantity'], item_data['price']))
        
        # Deduct stock
        for item_data in order_items_data:
            cursor.execute(f"""
                UPDATE {item_data['category']} SET stock = stock - %s 
                WHERE product_id = %s AND stock >= %s
                RETURNING stock
            """, (item_data['quantity'], item_data['product_id'], item_data['quantity']))
            result = cursor.fetchone()
            if not result:
                raise Exception(f"Insufficient stock, deduction failed: {item_data['category']}_{item_data['product_id']}")
        
        # Deduct user balance
        cursor.execute("""
            UPDATE users SET balance = balance - %s 
            WHERE user_id = %s AND balance >= %s
            RETURNING balance
        """, (total_price, user_id, total_price))
        result = cursor.fetchone()
        if not result:
            raise Exception("Insufficient balance, deduction failed")
        
        # Commit transaction
        conn.commit()
        return order_id
        
    except Exception as e:
        # Rollback transaction
        conn.rollback()
        raise e
    finally:
        cursor.close()
        conn.close()

def get_orders(user_id, page=1, per_page=10):
    """Get user orders"""
    conn, cursor = get_db_cursor()
    try:
        offset = (page - 1) * per_page
        cursor.execute("""
            SELECT * FROM orders WHERE user_id = %s
            ORDER BY created_at DESC
            LIMIT %s OFFSET %s
        """, (user_id, per_page, offset))
        
        orders = [dict(row) for row in cursor.fetchall()]
        
        # Get order items for each order
        for order in orders:
            cursor.execute("""
                SELECT oi.*, 
                       CASE 
                           WHEN oi.category = 'vegetables' THEN v.name
                           WHEN oi.category = 'meats' THEN m.name
                           WHEN oi.category = 'fruits' THEN f.name
                           WHEN oi.category = 'seafood' THEN s.name
                       END as name
                FROM order_items oi
                LEFT JOIN vegetables v ON oi.category = 'vegetables' AND oi.product_id = v.product_id
                LEFT JOIN meats m ON oi.category = 'meats' AND oi.product_id = m.product_id
                LEFT JOIN fruits f ON oi.category = 'fruits' AND oi.product_id = f.product_id
                LEFT JOIN seafood s ON oi.category = 'seafood' AND oi.product_id = s.product_id
                WHERE oi.order_id = %s
            """, (order['order_id'],))
            
            order_items = [dict(row) for row in cursor.fetchall()]
            order['items'] = order_items
        
        # Get total count
        cursor.execute("SELECT COUNT(*) FROM orders WHERE user_id = %s", (user_id,))
        total = cursor.fetchone()['count']
        
        return orders, total
    finally:
        cursor.close()
        conn.close()

def get_order_detail(order_id, user_id):
    """Get order detail"""
    conn, cursor = get_db_cursor()
    try:
        cursor.execute("""
            SELECT * FROM orders WHERE order_id = %s AND user_id = %s
        """, (order_id, user_id))
        order = cursor.fetchone()
        
        if order:
            # Get order items
            cursor.execute("""
                SELECT * FROM order_items WHERE order_id = %s
            """, (order_id,))
            order_items = [dict(row) for row in cursor.fetchall()]
            
            order_dict = dict(order)
            order_dict['items'] = order_items
            return order_dict
        
        return None
    finally:
        cursor.close()
        conn.close()
